package com.hy.dao;

import com.hy.bean.Card;
import com.hy.bean.RespBean;
import com.hy.utils.DBManager;
import com.hy.vo.CardUser;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.util.List;
import java.util.Map;

/**
* @author zhangzhengwei2006
* @description 针对表【card(帖子表)】的数据库操作Mapper
* @createDate 2024-01-05 11:06:17
* @Entity com.hy.bean.Card
*/
public class CardDao {

    private QueryRunner queryRunner = new QueryRunner();

    // 查询所有
    public List<CardUser> selectAll() throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select ca.id as cid,ca.title,ca.review_status,ca.card_content,ca.create_time,uu.user_name from card ca join ums_user uu on ca.user_id = uu.id and ca.create_id = uu.id";
        List<CardUser> cardList = queryRunner.query(conn, sql, new BeanListHandler<>(CardUser.class));
        DbUtils.close(conn);
        return cardList;
    }

    // 分页查询
    //select ca.id as cid,ca.title,ca.review_status,ca.card_content,ca.create_time,uu.user_name from card ca join ums_user uu on ca.user_id = uu.id and ca.create_id = uu.id where ca.review_status = '0' order by ca.review_status limit ?,?
    public List<CardUser> selectPage(Integer currentPage, Integer pageSize) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select ca.id as cid,ca.title,ca.review_status,ca.card_content,ca.create_time,uu.user_name from card ca join ums_user uu on ca.user_id = uu.id  where ca.review_status = '0' limit ?,?";
        List<CardUser> cardList = queryRunner.query(conn, sql, new BeanListHandler<>(CardUser.class),(currentPage-1)*pageSize,pageSize);

        DbUtils.close(conn);
        return cardList;
    }

    // 修改状态
    public RespBean updateStatusByid(Integer id, String status) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "update card set review_status = ? where id = ?";
        int update = queryRunner.update(conn, sql, status,id);
        DbUtils.close(conn);
        if (update == 1)
            return RespBean.success("修改成功");
        return RespBean.success("修改失败");
    }

    public int updateStatuByid(Integer id, String status) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "update card set review_status = ? where id = ?";
        int update = queryRunner.update(conn, sql, status,id);
        DbUtils.close(conn);
        return update;
    }

    // 查询总条数
    public Long selectTotal() throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select count(ca.id) from card ca join ums_user uu on ca.user_id = uu.id and ca.create_id = uu.id where ca.review_status = '0'";
        Long countNum = queryRunner.query(conn, sql, new ScalarHandler<>());
        DbUtils.close(conn);
        return countNum;
    }


    //消息信息
    public List<Map<String, Object>> selectAllx() throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select * from card c left join ums_user u on c.user_id = u.id";
        List<Map<String, Object>> mapList = queryRunner.query(conn, sql, new MapListHandler());
        DbUtils.close(conn);
        return mapList;
    }

    //消息 关注用户
    public List<Map<String, Object>> selectAllASAP() throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select distinct u.user_name,u.img from ums_user u left join follow f on u.id = f.user1_id where f.user_id";
        List<Map<String, Object>> mapList = queryRunner.query(conn, sql, new MapListHandler());
        DbUtils.close(conn);
        return mapList;
    }


    //根据关1
    public List<Map<String,Object>> selectFollow(Integer id, Integer limit) throws Exception {
        System.out.println("selectFollow=================>"+id);
        Connection conn = DBManager.getConn();
        String sql = "select c.*,u.img,u.user_name from card c " +
                "INNER JOIN ums_user u on c.user_id = u.id where u.tombstone = '0' and c.tombstone = '0' and user_id in \n" +
                "(select f.user1_id from follow f " +
                "INNER JOIN ums_user u on f.user_id = u.id \n" +
                "INNER JOIN ums_user u1 on f.user1_id = u1.id where " +
                "f.tombstone = '0' and u.tombstone = '0' and u1.tombstone = '0' and u.id = ?) and c.review_status = '1' and c.collect_status = '0' and  c.favor_status = '0' and c.user_id <> ? limit 0,?";
        List<Map<String,Object>> query = queryRunner.query(conn, sql, new MapListHandler(), id,id,limit);
        DbUtils.close(conn);
        return query;

    }


    //根据推荐查询
    public List<Map<String,Object>> selectRecommend(Integer id,Integer limit) throws Exception {
        Connection conn = DBManager.getConn();

        String sql = "select c.*,u.user_name,u.img from card c \n" +
                "      INNER JOIN ums_user u on u.id = c.user_id\n" +
                "      where c.id not in (select cid from table_like where create_id = ?  and tombstone = '0') \n" +
                "     and c.tombstone = '0'  and u.tombstone = '0' and c.review_status = '1'and collect_status = '0' and  favor_status = '0' and c.user_id <> ? order by  recommend desc limit 0,?";
        List<Map<String, Object>> query = queryRunner.query(conn, sql, new MapListHandler(), id,id,limit);
        DbUtils.close(conn);
        return query;
    }

    public boolean modify(int cid) throws Exception {
        Connection conn = DBManager.getConn();

        String sql = "update card set collect_status = '1' where id = ?";
        int count = queryRunner.update(conn, sql, cid);

        DbUtils.close(conn);
        return  count>0?true:false;
    }

    public boolean modify2(int cid) throws Exception {
        Connection conn = DBManager.getConn();

        String sql = "update card set collect_status = '0' where id = ?";

        int count = queryRunner.update(conn, sql, cid);
        DbUtils.close(conn);
        return count>0?true:false;
    }

    public boolean modifyfa(int cid) throws Exception {
        Connection conn = DBManager.getConn();

        String sql = "update card set favor_status ='1' where id = ?";
        int count = queryRunner.update(conn, sql, cid);

        DbUtils.close(conn);
        return count>0?true:false;
    }
    public boolean modifyfa2(int cid) throws Exception {
        Connection conn = DBManager.getConn();

        String sql = "update card set favor_status ='0' where id = ?";
        int count = queryRunner.update(conn, sql, cid);

        DbUtils.close(conn);
        return count>0?true:false;
    }


    public List<Card> selectSy(String str) throws Exception {
        Connection conn = DBManager.getConn();

        String sql = "select * from card where title like ?";
        BeanListHandler<Card> blh = new BeanListHandler<>(Card.class);
        List<Card> cardList = queryRunner.query(conn, sql, blh, "%"+str+"%");

        DbUtils.close(conn);
        return cardList;

    }

    public static void main(String[] args) throws Exception {
        CardDao cardDao = new CardDao();
        boolean modify = cardDao.modify(5);
        System.out.println(modify);
    }

}




